CREATE PROCEDURE dbo.trace as DECLARE @queue_handle int, --queue handle to refer to this trace by @column_value int, --data column bitmask @trace_name varchar(128), @server_name varchar(128), @object_name varchar(255), @duration char(9) set @duration = '023:59:59' -- 'hhh:mm:ss' set @trace_name = object_name(@@PROCID) + '_' + @@servername + '_' + cast(datepart(yyyy,getdate()) as varchar(4)) + case when datalength(cast(datepart(mm,getdate()) as varchar(2))) = 1 then '0' else '' end + cast(datepart(mm,getdate()) as varchar(2)) + case when datalength(cast(datepart(dd,getdate()) as varchar(2))) = 1 then '0' else '' end + cast(datepart(dd,getdate()) as varchar(2)) + case when datalength(cast(datepart(hh,getdate()) as varchar(2))) = 1 then '0' else '' end + cast(datepart(hh,getdate()) as varchar(2)) + case when datalength(cast(datepart(mi,getdate()) as varchar(2))) = 1 then '0' else '' end + cast(datepart(mi,getdate()) as varchar(2)) set @server_name = null set @object_name = 'F:\MSSQL7\log\' + @trace_name + '.TRC' /**************** Create a queue ****************/ --Set the column mask for the data columns to capture /* 1 Text data 2 Binary data 4 Database ID 8 Transaction ID 16 Connection ID 32 Microsoft Windows NT® username 64 Windows NT domain name 128 Host name 256 Host process ID 512 Application name 1024 SQL Login name 2048 SQL process ID (SPID) 4096 Duration 8192 Start time 16384 End time 32768 Reads 65536 Writes 131072 CPU usage 524288 Severity 1048576 Event Subclass 2097152 Object ID 8388608 Index ID 16777216 Integer data 33554432 Server name 67108864 Event Class */ SET @column_value = 1|4|32|64|128|256|512|1024|2048|4096|8192|16384|32768|65536|131072|524288|1048576|16777216|33554432|67108864 EXEC master.dbo.xp_trace_addnewqueue 1000, -- 1 to 20,000, number of buffered entries 5, -- timeout in seconds (will miss activity if timeout is exceeded) (default=5) 95, -- thread boost (controls trace thread's priority) (default=95%) 90, -- thread reduce (controls trace thread's priority) (default=90%) @column_value, @queue_handle OUTPUT raiserror ('Trace ''%s'' is started. Handle = %d',1,1,@trace_name, @queue_handle) WITH NOWAIT, LOG /******************* Set the event classes to trace ****************************/ /*================================================================================== 0 TraceStart Given trace has been put into started state. 1 TracePause Given trace has been put into a paused state. 2 TraceRestart Given trace has been put into a restarted state. 3 TraceAutoPause Given trace has been put into an automatic pause state. 4 TraceAutoRestart Given trace has been put into an automatic restart state. 5 TraceStop Given trace has been put into a stopped state. 6 EventRequired Given trace has been put into an events to trace changed state. 7 FilterChanged Given trace has been put into a filter changed state. 8 8 Reserved. 9 9 Reserved. 10 RPC:Completed Occurs when a remote procedure call (RPC) has been completed. 11 RPC:Starting Occurs when an RPC has been started. 12 SQL:BatchCompleted Occurs when a Transact-SQL batch has been completed. 13 SQL:BatchStarting Occurs when a Transact-SQL batch is starting. 14 Connect Collects all connection events, such as when a client requests a connection to a server running Microsoft® SQL Server™. 15 Disconnect Collects all disconnect events, such as when a client issues a disconnect command. 16 Attention Collects all attention events, such as client-interrupt requests or broken client connections. 17 ExistingConnection Detects all users that were logged on before the trace started. 18 ServiceControl Tracks all server control events, such as server paused or restart events. 19 DTCTransaction Tracks MS DTC coordinated transactions between two or more databases. For more information, see How to use Microsoft Distributed Transaction Coordinator (ODBC). 20 LoginFailed Indicates that a login attempt to SQL Server from a client failed. 21 EventLog Indicates events logged in the Microsoft Windows NT® application log. 22 ErrorLog Indicates error events logged in the SQL Server error log. 23 Lock:Released Indicates that a lock on a resource, such as a page, has been released. 24 Lock:Acquired Indicates acquisition of a lock on a resource, such as a data page. 25 Lock:Deadlock Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns. 26 Lock:Cancel Indicates that the acquisition of a lock on a resource has been canceled (for example, due to a deadlock). 27 Lock:Timeout Indicates that a request for a lock on a resource, such as a page, has timed out due to another transaction holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT function, and can be set with the SET LOCK_TIMEOUT statement. 28 Insert Occurs before an INSERT statement is executed. 29 Update Occurs before an UPDATE statement is executed. 30 Delete Occurs before DELETE statement is executed. 31 Select Occurs before SELECT statement is executed. 32 ConnectionBeingKilled Indicates that the connection is being killed. 33 Exception Indicates that an exception has occurred in SQL Server. 34 SP:CacheMiss Indicates when a stored procedure is not found in the procedure cache. 35 SP:CacheInsert Indicates when an item is inserted into the procedure cache. 36 SP:CacheRemove Indicates when an item is removed from the procedure cache. 37 SP:Recompile Indicates that a stored procedure is recompiling. 38 SP:CacheHit Indicates when a stored procedure is found in the procedure cache. 39 SP:ExecContextHit Indicates when the execution version of a stored procedure has been found in the procedure cache. 40 SQL:StmtStarting Occurs when the Transact-SQL statement is starting. 41 SQL:StmtCompleted Occurs when the Transact-SQL statement has completed. 42 SP:Starting Indicates when the stored procedure has started. 43 SP:Completed Indicates when the stored procedure has completed. 44 SP:StmtStarting Indicates when a statement within a stored procedure is starting. 45 SP:StmtCompleted Indicates when a statement within a stored procedure has completed. 46 Object:Created Indicates when an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements. 47 Object:Deleted Indicates when an object has been deleted, such as in DROP INDEX and DROP TABLE statements. 48 Object:Opened Indicates when an object has been accessed, such as for SELECT, INSERT, or DELETE statements. 49 Object:Closed Indicates when an object has been closed, such as the end of a SELECT, INSERT, or DELETE statement. 50 SQLTransaction Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements. 51 Scan:Started Indicates when a table or index scan has started. 52 Scan:Stopped Indicates when a table or index scan has stopped. 53 CursorOpen Indicates when a cursor has been opened on a Microsoft SQL Server table by ODBC or DB-Library. 54 TransactionLog Tracks when transactions are written to the transaction log. 55 HashWarning Indicates that a hashing operation (for example, hash join, hash aggregate, hash union, and hash distinct) that is not processing on a buffer partition has reverted to an alternate plan. This can occur because of recursion depth, data skew, trace flags, or bit counting. 56 Reserved. 57 Reserved. 58 Auto-UpdateStats Collects the events associated with the automatic updating of index statistics. 59 Lock:Deadlock Chain Is produced for each of the events leading up to the deadlock. 60 Lock:Escalation Indicates that a finer-grained lock has been converted to a coarser-grained lock (for example, a row lock that is escalated or converted to a page lock). 61 OLEDB Errors Indicates that an OLE DB error has occurred. 62 Replay Error returned by ODBC. 63 Replay Internal Error Internal replay error. 64 Replay Result Set Indicates the beginning of a result set returned by a query. 65 Replay Result Row Produced for each row returned from a replay result set. 66 Reserved. 67 Execution Warnings Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure. 68 Execution Plan Displays the plan tree of the Transact-SQL statement being executed. 69 Sort Warnings Indicates sort operations that do not fit into memory. Does not include sort operations involving the creating of indexes; only sort operations within a query (such as an ORDER BY clause used in a SELECT statement). 70 CursorPrepare Indicates when a cursor has been prepared on a Transact-SQL statement by ODBC or DB-Library. 71 Prepare SQL ODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement for use. 72 Exec Prepared SQL ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement. 73 Unprepare SQL ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement. 74 CursorExecute A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed. 75 CursorRecompile A cursor that is opened on a Transact-SQL statement by ODBC or DB-Library has been recompiled either directly or due to a schema change. 76 CursorImplicitConversion A cursor on a Transact-SQL statement is converted by SQL Server from one type to another. 77 CursorUnprepare A prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC, OLE DB, or DB-Library. 78 CursorClose A cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is closed. 79 Missing Column Statistics Column statistics that could have been useful for the query optimizer are not available. 80 Missing Join Predicate A query is being executed that has no join predicate. This could result in a long-running query. 81 Server Memory Change Microsoft SQL Server memory usage has increased or decreased by either 1 MB or 5 percent of the maximum server memory, whichever is greater. 82 UserConfigurable:1 User configurable event. Collects event data defined by the user. For more information about user configurable events, see master.dbo.xp_trace_generate_event. 83 UserConfigurable:2 User configurable event. Collects event data defined by the user. 84 UserConfigurable:3 User configurable event. Collects event data defined by the user. 85 UserConfigurable:4 User configurable event. Collects event data defined by the user. 86 UserConfigurable:5 User configurable event. Collects event data defined by the user. ==================================================================================*/ --To look up the names, execute master.dbo.xp_trace_geteventnames. --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 41, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 45, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 10, 1 EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 11, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 12, 1 EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 13, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 14, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 16, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 17, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 22, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 33, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 37, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 53, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 67, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 69, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 75, 1 --EXEC master.dbo.xp_trace_seteventclassrequired @queue_handle, 80, 1 /************************************* Set filters ***************************************/ --EXEC master.dbo.xp_trace_setappfilter {queue_handle, 'include_only_applications','exclude_applications'} --EXEC master.dbo.xp_trace_setappfilter @queue_handle, N'', N'SQL Server Profiler%' --EXEC master.dbo.xp_trace_setconnectionidfilter {queue_handle, connection_ID} --EXEC master.dbo.xp_trace_setcpufilter {queue_handle, minimum_cpu, maximum_cpu} --EXEC master.dbo.xp_trace_setdbidfilter @queue_handle, 9 -- dbid --EXEC master.dbo.xp_trace_setdurationfilter {queue_handle, minimum_duration, maximum_duration} --EXEC master.dbo.xp_trace_seteventclassrequired {queue_handle, event_class, isrequired} --EXEC master.dbo.xp_trace_seteventfilter {queue_handle, event_class, 'include_only_events', 'exclude_events'} --EXEC master.dbo.xp_trace_sethostfilter {queue_handle, 'include_hosts', 'exclude_hosts'} --EXEC master.dbo.xp_trace_sethpidfilter {queue_handle, process_ID} --EXEC master.dbo.xp_trace_setindidfilter {queue_handle, index_ID} --EXEC master.dbo.xp_trace_setntdmfilter {queue_handle, 'include_domains', 'exclude_domains'} --EXEC master.dbo.xp_trace_setntnmfilter {queue_handle, 'include_NTusers', 'exclude_NTusers'} --EXEC master.dbo.xp_trace_setobjidfilter {queue_handle, object_ID} --EXEC master.dbo.xp_trace_setreadfilter {queue_handle, minimum_read, maximum_read} --EXEC master.dbo.xp_trace_setserverfilter {queue_handle, 'include_servers', 'exclude_servers'} --EXEC master.dbo.xp_trace_setseverityfilter {queue_handle, minimum_severity, maximum_severity} --EXEC master.dbo.xp_trace_setspidfilter {queue_handle, spid} --EXEC master.dbo.xp_trace_setsysobjectsfilter {queue_handle, sysobjects_value} --EXEC master.dbo.xp_trace_settextfilter @queue_handle, N'%xp_wsod%', N'' EXEC master.dbo.xp_trace_setuserfilter {queue_handle, N'alert,avsearch,batchjobs,bqserver,BridgeServer,cs_lookups,CS_QRY,customer,djfeed,fc_data,fclink,fc_loader,fitch_upload,gen_feed,mailroom,sch500port,vick_load', N''} --EXEC master.dbo.xp_trace_setwritefilter {queue_handle, minimum_write, maximum_write} -- get the last 100 queries if exception occurs (will store to BlackBox.TRC if severity > 16) --EXEC master.dbo.xp_trace_setqueryhistory [[@local_variable =] value] /************************************* Configure the queue ****************************************/ --EXEC master.dbo.xp_trace_setqueuedestination {queue_handle, destination, value {[, 'server'] [, 'object']}} -- parm 2 --2 To file --3 To application log --4 To table --5 To forwarded server -- parm 3 -- 1 = enabled, 0 = disabled -- parm 4 -- NULL unless parm2 is 3 or 5 -- parm 5 -- NULL unless parm2 is2 or 4 -- provide full path for file -- will create file or table if doesn't exist EXEC master.dbo.xp_trace_setqueuedestination @queue_handle, 2, 1, @server_name, @object_name /********************************** Start the Trace ***********************************************/ EXEC master.dbo.xp_trace_startconsumer @queue_handle /******************************** Save queue definition *******************************************/ -- place definition in registry (use xp_trace_loadqueuedefinition to get it back) --EXEC master.dbo.xp_trace_savequeuedefinition @queue_handle, @trace_name, 1 -- 1 = shared, 0 = private /*************************** set the time for the trace to run ************************************/ WAITFOR DELAY @duration /*********************************** Stop the trace ***********************************************/ EXEC master.dbo.xp_trace_destroyqueue @queue_handle /* --Pause the trace master.dbo.xp_trace_pausequeue 16 -- [Queue Handle] --Get a local copy of the output file to view it master.dbo.xp_cmdshell 'dir F:\MSSQL7\log\WhyDoesDbgFail.trc' master.dbo.xp_cmdshell 'del F:\MSSQL7\log\WhyDoesDbgFail.trc' master.dbo.xp_cmdshell 'copy F:\MSSQL7\log\WhyDoesDbgFail.trc \\cs234_ltp\d$' --Restart paused trace master.dbo.xp_trace_restartqueue 16 -- [Queue Handle] */ GO